#!/usr/bin/env python
# -*- coding:utf-8 -*-
import configparser
import json

from conn.MyRedisFactory import RedisConfig
from excel.EGI.EgiBaseTabTop import EgiBaseTabTop
from excel.EGI.EgiDictUtil import EgiDictUtil
from excel.EGI.EgiObj import EgiObj
from excel.PruTuutDb import PruTuutDb

f = r'setting.ini'
config = configparser.RawConfigParser()
config.read(f, encoding='UTF8')
rds_cfg = RedisConfig(host=config['redis']['host'],
                      port=config['redis']['port'],
                      passwd=config['redis']['password'])
rds_conn = RedisConfig.get_pool(rds_cfg)

base_table_top = EgiBaseTabTop()
pru_tuut_db_conn = PruTuutDb.get_pool()
dict_db = EgiDictUtil(pru_tuut_db_conn)
# **
# 参考计算EXCEL原文件中列表右侧的数据列
# ----------------------------------------------------------------------------------------------------------
TEST_YEAR = range(100)  # 测试年限为100的数据


# ----------------------------------------------------------------------------------------------------------
class EgiBaseTabCalStd:
    res = []

    def __init__(self):
        return

    # **
    # 计算 每年供款 Annual Contribution
    # Params:
    # 翌年歲 C36 : ANB
    # Contribution Q36 : Contribution
    # Contribution1 X36 : Contribution1
    # Contribution2 AE36 : Contribution2
    # Contribution3 AL36 : Contribution3
    # Contribution4 AS36 : Contribution4
    # Contribution5 AZ36 : Contribution5
    # Rebate O36 : Rebate
    def get_Annual_Contribution(self, anb, total_contribution, rebate):
        # 参考计算公式
        # =IF(C36<=101,Q36+X36+AE36+AL36+AS36+AZ36-O36,0)
        return total_contribution - rebate if anb <= 151 else 0

    # **
    # 计算 現金提取 入息 Cash Payment Income
    # Params:
    # 翌年歲 C36 : ANB
    # R36 : G m income
    # S36 : nG m income
    # Y36 : G m income1
    # Z36 : nG m income1
    # AF36 : G m income2
    # AG36 : nG m income2
    # AM36 : G m income3
    # AN36 : nG m income3
    # AT36 : G m income4
    # AU36 : nG m income4
    # BA36 : G m income5
    # BB36 : nG m income5
    def get_Cash_Payment_Income(self, anb, total_g_m_income, total_ng_m_income):
        # 参考计算公式
        # =IF(C36<=151,R36+S36+Y36+Z36+AF36+AG36+AM36+AN36+AT36+AU36+BA36+BB36,0)
        return total_g_m_income + total_ng_m_income if anb <= 151 else 0

    # **
    # 计算 現金提取 累積入息 Cash Payment Cumulative Income
    # Params:
    # 翌年歲 C36 : ANB
    # 累積入息 F35 : Cumulative Income
    # 入息 E36 : Cash Payment Income
    def get_Cash_Payment_Cumulative_Income(self, anb, cumulative_income, income):
        # 参考计算公式
        # =IF(C36<=151,F35+E36,0)
        return cumulative_income + income if anb <= 151 else 0

    # **
    # 计算 現金提取 退保價值 Cash Payment Surrender Value
    # Params:
    # 翌年歲 C36 : ANB
    # T36 : G CV
    # U36 ：Terminal
    # AA36 : G CV1
    # AB36 ：Terminal1
    # AH36 : G CV2
    # AI36 ：Terminal2
    # AO36 : G CV3
    # AP36 ：Terminal3
    # AV36 : G CV4
    # AW36 ：Terminal4
    # BC36 : G CV5
    # BD36 ：Terminal5
    def get_Cash_Payment_Surrender_Value(self, anb, total_g_cv, total_terminal):
        # 参考计算公式
        # =IF(C36<=151,T36+U36+AA36+AB36+AH36+AI36+AO36+AP36+AV36+AW36+BC36+BD36,0)
        return total_g_cv + total_terminal if anb <= 151 else 0

    # **
    # 计算 積存 入息 Accumulation Income
    # Params:
    # 翌年歲 C36 : Year
    # 入息 E36 : Income
    def get_Accumulation_Income(self, year, income):
        # 参考计算公式
        # =IF(B36<=10,E36,0)
        return income if year <= 10 else 0

    # **
    # 计算 積存 積存結餘 Accumulation Cumulative Income
    # Params:
    # 翌年歲 C36 : ANB
    # V36 : G acc
    # W36 : nG acc
    # AC36 : G acc1
    # AD36 : nG acc1
    # AJ36 : G acc2
    # AK36 : nG acc2
    # AQ36 : G acc3
    # AR36 : nG acc3
    # AX36 : G acc4
    # AY36 : nG acc4
    # BE36 : G acc5
    # BF36 : nG acc5
    def get_Accumulation_Cumulative_Income(self, anb, total_g_acc, total_ng_acc):
        # 参考计算公式
        # =IF(C36<=151,V36+W36+AC36+AD36+AJ36+AK36+AQ36+AR36+AX36+AY36+BE36+BF36,0)
        return total_g_acc + total_ng_acc if anb <= 151 else 0

    # **
    # 计算 積存 退保價值 Accumulation Surrender Value
    # Params:
    # 翌年歲 C36 : ANB
    # 退保價值 G36 : Surrender Value
    # 積存結餘 I36 : Accumulation Balance
    def get_Accumulation_Surrender_Value(self, anb, surrender_value, accumulation_balance):
        # 参考计算公式
        # =IF(C36<=151,G36+I36,0)
        return surrender_value + accumulation_balance if anb <= 151 else 0

    # **
    # 计算 Withdraw cashflow
    # Params:
    # 翌年歲 C36 : ANB
    # IRR 翌年歲 D6 : IRR ANB
    # 入息 E35 : Cash_Payment last Income
    # 累積入息  E36 : Cash_Payment Income
    # 每年供款 D36 : Annual Contribution
    # 退保價值  G35 : Cash Payment Surrender Value
    def get_Withdraw_cashflow(self, anb, irr_anb, last_income, income, annual_contribution, surrender_value, ):
        # 参考计算公式
        # IF(C36<D6,E35/2+E36/2-D36,IF(C36=D6,E35+G35,0))
        # STEP 1 IF(C36<D6,E35/2+E36/2-D36
        if anb < irr_anb:
            return last_income / 2 + income / 2 - annual_contribution
        else:
            # STEP 2 IF(C36=D6,E35+G35,0)
            return last_income + surrender_value if anb == irr_anb else 0

    # **
    # 计算 Accu cashflow
    # Params:
    # 翌年歲 C36 : ANB
    # IRR 翌年歲 D6 : IRR ANB
    # 入息 H35 : Accumulation last Income
    # 累積入息  H36 : Accumulation Income
    # 每年供款 D36 : Annual Contribution
    # 退保價值  J35 :Annual Surrender_Value
    def get_Accu_cashflow(self, anb, irr_anb, last_income, income, annual_contribution, surrender_value):
        # 参考计算公式
        # =IF(C36<D6,H35/2+H36/2-D36,IF(C36=D6,J35,0))
        # STEP 1 IF(C36<D6,H35/2+H36/2-D36
        if anb < irr_anb:
            return last_income / 2 + income / 2 - annual_contribution
        else:
            # STEP 2 IF(C36=D6,J35,0)
            return surrender_value if anb == irr_anb else 0

    # **
    # 计算 Rebate
    # Params:
    # 推廣優惠 H8 : Promotion
    # 实际比率 D22: Actual annual contribution
    # 显示汇率 D27: Display rate
    def get_Rebate(self, promotion, actual_annual_contribution, display_rate):
        # 参考计算公式
        # =IF(LOWER(H8)="y",IF(D22>=1000000,7%,IF(D22>=750000,5%,IF(D22>=500000,3%,IF(D22>=25000,1%,0)))),0)*D22*D27
        if not str(promotion).lower() == "y":
            return 0 * actual_annual_contribution * display_rate
        else:
            cal_actual_ratio = 0
            # STEP 1
            # IF(D22>=1000000,7%,IF(D22>=750000,5%,IF(D22>=500000,3%,IF(D22>=25000,1%,0))))
            if actual_annual_contribution >= 1000000:
                cal_actual_ratio = float(7 / 100)
            elif actual_annual_contribution >= 750000:
                cal_actual_ratio = float(5 / 100)
            elif actual_annual_contribution >= 500000:
                cal_actual_ratio = float(3 / 100)
            elif actual_annual_contribution >= 25000:
                cal_actual_ratio = float(1 / 100)
            # STEP 2
            # cal_actual_ratio*D22*D27
        return cal_actual_ratio * actual_annual_contribution * display_rate

    # **
    # 计算 Contribution
    # Params:
    # 翌年歲 C36 : ANB
    # 年期 B35 : Year
    # 供款期 D14 : Payment Term
    # 实际年度贡献 D22: Actual annual contribution
    # 显示汇率 D27: Display rate
    def get_Contribution(self, year, payment_term, actual_annual_contribution, display_rate):
        # 参考计算公式
        # =IF(B35<=D14,D22*D27,0)
        return actual_annual_contribution * display_rate if year <= payment_term else 0

    # **
    # 计算 Contribution X
    # Params:
    # 重複儲蓄計劃 H5 : Repeat Saving Plan
    # 重複儲蓄增長率 H6: Repeat Saving Growth Rate
    # X次数 X33 : run_times
    # 年期 B36 : Year
    # 供款期 D14: Payment Term
    # 显示汇率 Q36: Display rate
    def get_Contribution_x(self, year, repeat_saving_plan, run_times, repeat_saving_growth_rate, payment_term,
                           contributions, ):
        # 参考计算公式
        # =IF(H5>=X33,IF(B36>D14*X33,VLOOKUP(B36-D14*X33,B36:W185,COLUMN(Q36)-COLUMN(B36)+1)*(1+H6)^X33,0),0)
        # STEP 1 VLOOKUP(B36-D14*X33,B36:W185,COLUMN(Q36)-COLUMN(B36)+1) ==> Contribution
        if not repeat_saving_plan >= run_times:
            return 0
        else:
            # STEP 2 IF(B36>D14*X33,VLOOKUP(B36-D14*X33,B36:W185,COLUMN(Q36)-COLUMN(B36)+1)*(1+H6)^X33,0)
            if not year > payment_term * run_times:
                return 0
            else:
                contribution = 0
                # STEP 3 contribution*(1+H6)^X33
                if (year - payment_term * run_times) == 1:
                    contribution = contributions[0]
                return contribution * (1 + repeat_saving_growth_rate) ** run_times

    # **
    # 计算 G m income
    # Params:
    # 翌年歲 C36 : ANB
    # 年期 B35 : Year
    # EGI index D17 : 169
    # 实际比率 D23: Actual ratio
    # 显示汇率 D27: Display rate
    def get_G_m_income(self, anb, year, actual_ratio, display_rate):
        # 参考计算公式
        # =IF(C36<=151,VLOOKUP(B35,data4!A2:HI153,D17,FALSE)*D23*D27,0)
        # STEP 1
        # VLOOKUP(B35,data4!A2:HI153,D17,FALSE)
        res = dict_db.vlookup_EGI_by_anb_and_col(args=[year, anb], col=EgiObj.EGI['G_m_income'])
        if not res['code'] == 200:
            return 0
        actual_ratio = float(round(actual_ratio, 6))
        cal_G_m_income = res['data']
        if not anb <= 151:
            return 0
        else:
            # STEP 2 cal_G_m_income *D23*D27
            return cal_G_m_income * actual_ratio * display_rate

    # **
    # 计算 G m income X
    # Params:
    # 重複儲蓄計劃 H5 : Repeat Saving Plan
    # 重複儲蓄增長率 H6: Repeat Saving Growth Rate
    # X次数 X33 : run_times
    # 年期 B36 : Year
    # 供款期 D14: Payment Term
    # 显示汇率 Q36: Display rate
    def get_G_m_income_x(self, year, repeat_saving_plan, run_times, repeat_saving_growth_rate, payment_term,
                         g_m_incomes, ):
        # 参考计算公式
        # =IF(H5>=X33,IF(B36>D14*X33,VLOOKUP(B36-D14*X33,B36:W185,COLUMN(Q36)-COLUMN(B36)+2)*(1+H6)^X33,0),0)
        # STEP 1 VLOOKUP(B36-D14*X33,B36:W185,COLUMN(Q36)-COLUMN(B36)+2) ==> G_m_income
        if not int(repeat_saving_plan) >= run_times:
            return 0
        else:
            # STEP 2.1 IF(B36>D14*X33,VLOOKUP(B36-D14*X33,B36:W185,COLUMN(Q36)-COLUMN(B36)+2)*(1+H6)^X33,0)
            if not year > payment_term * run_times:
                return 0
            else:
                # STEP 2.2 VLOOKUP(B36-D14*X33,B36:W185,COLUMN(Q36)-COLUMN(B36)+2)
                _val_idx = (year - payment_term * run_times)
                g_m_income = g_m_incomes[_val_idx]
                # STEP 3 G_m_income*(1+H6)^X33
                return g_m_income * (1 + repeat_saving_growth_rate) ** run_times

    # **
    # 计算 nG m income
    # Params:
    # 翌年歲 C36 : ANB
    # 年期 B35 : Year
    # EGI index D17 : 169
    # 实际比率 D23: Actual ratio
    # 显示汇率 D27: Display rate
    def get_nG_m_income(self, anb, year, actual_ratio, display_rate):
        # 参考计算公式
        # =IF(C36<=151,VLOOKUP(B35,data4!A2:HI153,D17+1,FALSE)*D23*D27,0)
        res = dict_db.vlookup_EGI_by_anb_and_col(args=[year, anb], col=EgiObj.EGI['nG_m_income'])
        if not res['code'] == 200:
            return 0
        actual_ratio = float(round(actual_ratio, 6))
        cal_nG_m_income = res['data']
        if not anb <= 151:
            return 0
        else:
            # STEP 2 cal_G_m_income *D23*D27
            return cal_nG_m_income * actual_ratio * display_rate

    # **
    # 计算 nG m income X
    # Params:
    # 重複儲蓄計劃 H5 : Repeat Saving Plan
    # 重複儲蓄增長率 H6: Repeat Saving Growth Rate
    # X次数 X33 : run_times
    # 年期 B36 : Year
    # 供款期 D14: Payment Term
    # 显示汇率 Q36: Display rate
    def get_nG_m_income_x(self, year, repeat_saving_plan, run_times, repeat_saving_growth_rate, payment_term,
                          nG_m_incomes, ):
        # 参考计算公式
        # =IF(H5>=X33,IF(B36>D14*X33,VLOOKUP(B36-D14*X33,B36:W185,COLUMN(Q36)-COLUMN(B36)+3)*(1+H6)^X33,0),0)
        # STEP 1 VLOOKUP(B36-D14*X33,B36:W185,COLUMN(Q36)-COLUMN(B36)+3) ==> nG_m_income
        if not repeat_saving_plan >= run_times:
            return 0
        else:
            # STEP 2.1 IF(B36>D14*X33,*(1+H6)^X33,0)
            if not year > payment_term * run_times:
                return 0
            else:
                # STEP 2.2 VLOOKUP(B36-D14*X33,B36:W185,COLUMN(Q36)-COLUMN(B36)+3)
                _val_idx = (year - payment_term * run_times)
                nG_m_income = nG_m_incomes[_val_idx]
                # STEP 3 nG_m_income*(1+H6)^X33
                return nG_m_income * (1 + repeat_saving_growth_rate) ** run_times

    # **
    # 计算 G CV
    # Params:
    # 翌年歲 C36 : ANB
    # 年期 B35 : Year
    # EGI index D17 : 169
    # 实际比率 D23: Actual ratio
    # 显示汇率 D27: Display rate
    def get_G_CV(self, anb, year, actual_ratio, display_rate):
        # 参考计算公式
        # =IF(C36<=151,VLOOKUP(B35,data4!A2:HI153,D17+2,FALSE)*D23*D27,0)
        res = dict_db.vlookup_EGI_by_anb_and_col(args=[year, anb], col=EgiObj.EGI['G_CV'])
        if not res['code'] == 200:
            return 0
        actual_ratio = float(round(actual_ratio, 6))
        cal_G_CV = res['data']
        if not anb <= 151:
            return 0
        else:
            # STEP 2 cal_G_m_income *D23*D27
            return cal_G_CV * actual_ratio * display_rate

    # **
    # 计算 G CV
    # Params:
    # 重複儲蓄計劃 H5 : Repeat Saving Plan
    # 重複儲蓄增長率 H6: Repeat Saving Growth Rate
    # X次数 X33 : run_times
    # 年期 B36 : Year
    # 供款期 D14: Payment Term
    # 显示汇率 Q36: Display rate
    def get_G_CV_x(self, year, repeat_saving_plan, run_times, repeat_saving_growth_rate, payment_term, g_cvs, ):
        # 参考计算公式
        # =IF(H5>=X33,IF(B36>D14*X33,VLOOKUP(B36-D14*X33,B36:W185,COLUMN(Q36)-COLUMN(B36)+4)*(1+H6)^X33,0),0)
        # STEP 1 VLOOKUP(B36-D14*X33,B36:W185,COLUMN(Q36)-COLUMN(B36)+4) ==> G_CV
        if not repeat_saving_plan >= run_times:
            return 0
        else:
            # STEP 2.1 IF(B36>D14*X33,VLOOKUP(B36-D14*X33,B36:W185,COLUMN(Q36)-COLUMN(B36)+4)*(1+H6)^X33,0)
            if not year > payment_term * run_times:
                return 0
            else:
                # STEP 2.2 VLOOKUP(B36-D14*X33,B36:W185,COLUMN(Q36)-COLUMN(B36)+4)
                _val_idx = (year - payment_term * run_times)
                g_cv = g_cvs[_val_idx]
                # STEP 3 g_cv*(1+H6)^X33
                return g_cv * (1 + repeat_saving_growth_rate) ** run_times

    # **
    # 计算 Terminal
    # Params:
    # 翌年歲 C36 : ANB
    # 年期 B35 : Year
    # EGI index D17 : 169
    # 实际比率 D23: Actual ratio
    # 显示汇率 D27: Display rate
    def get_Terminal(self, anb, year, actual_ratio, display_rate):
        # 参考计算公式
        # =IF(C36<=151,VLOOKUP(B35,data4!A2:HI153,D17+3,FALSE)*D23*D27,0)
        res = dict_db.vlookup_EGI_by_anb_and_col(args=[year, anb], col=EgiObj.EGI['Terminal'])
        if not res['code'] == 200:
            return 0
        actual_ratio = float(round(actual_ratio, 6))
        cal_Terminal = res['data']
        if not anb <= 151:
            return 0
        else:
            # STEP 2 cal_Terminal *D23*D27
            return cal_Terminal * actual_ratio * display_rate

    # **
    # 计算 Terminal
    # Params:
    # 重複儲蓄計劃 H5 : Repeat Saving Plan
    # 重複儲蓄增長率 H6: Repeat Saving Growth Rate
    # X次数 X33 : run_times
    # 年期 B36 : Year
    # 供款期 D14: Payment Term
    # 显示汇率 Q36: Display rate
    def get_Terminal_x(self, year, repeat_saving_plan, run_times, repeat_saving_growth_rate, payment_term, terminals, ):
        # 参考计算公式
        # =IF(H5>=X33,IF(B36>D14*X33,VLOOKUP(B36-D14*X33,B36:W185,COLUMN(Q36)-COLUMN(B36)+5)*(1+H6)^X33,0),0)
        # STEP 1 VLOOKUP(B36-D14*X33,B36:W185,COLUMN(Q36)-COLUMN(B36)+5) ==> Terminal
        if not repeat_saving_plan >= run_times:
            return 0
        else:
            # STEP 2.1 IF(B36>D14*X33,VLOOKUP(B36-D14*X33,B36:W185,COLUMN(Q36)-COLUMN(B36)+5)*(1+H6)^X33,0)
            if not year > payment_term * run_times:
                return 0
            else:
                # STEP 2.2 VLOOKUP(B36-D14*X33,B36:W185,COLUMN(Q36)-COLUMN(B36)+5)
                _val_idx = (year - payment_term * run_times)
                terminal = terminals[_val_idx]
                # STEP 3 terminal*(1+H6)^X33
                return terminal * (1 + repeat_saving_growth_rate) ** run_times

    # **
    # 计算 G acc
    # Params:
    # 翌年歲 C36 : ANB
    # 年期 B35 : Year
    # EGI index D17 : 169
    # 实际比率 D23: Actual ratio
    # 显示汇率 D27: Display rate
    def get_G_acc(self, anb, year, actual_ratio, display_rate):
        # 参考计算公式
        # =IF(C36<=151,VLOOKUP(B35,data4!A2:HI153,D17+4,FALSE)*D23*D27,0)
        res = dict_db.vlookup_EGI_by_anb_and_col(args=[year, anb], col=EgiObj.EGI['G_acc'])
        if not res['code'] == 200:
            return 0
        actual_ratio = float(round(actual_ratio, 6))
        cal_G_acc = res['data']
        if not anb <= 151:
            return 0
        else:
            # STEP 2 cal_G_m_income *D23*D27
            return cal_G_acc * actual_ratio * display_rate

    # **
    # 计算 G acc
    # Params:
    # 重複儲蓄計劃 H5 : Repeat Saving Plan
    # 重複儲蓄增長率 H6: Repeat Saving Growth Rate
    # X次数 X33 : run_times
    # 年期 B36 : Year
    # 供款期 D14: Payment Term
    # 显示汇率 Q36: Display rate
    def get_G_acc_x(self, year, repeat_saving_plan, run_times, repeat_saving_growth_rate, payment_term, g_accs, ):
        # 参考计算公式
        # =IF(H5>=X33,IF(B36>D14*X33,VLOOKUP(B36-D14*X33,B36:W185,COLUMN(Q36)-COLUMN(B36)+6)*(1+H6)^X33,0),0)
        # STEP 1 VLOOKUP(B36-D14*X33,B36:W185,COLUMN(Q36)-COLUMN(B36)+6) ==> G_acc
        if not repeat_saving_plan >= run_times:
            return 0
        else:
            # STEP 2.1 IF(B36>D14*X33,VLOOKUP(B36-D14*X33,B36:W185,COLUMN(Q36)-COLUMN(B36)+6)*(1+H6)^X33,0)
            if not year > payment_term * run_times:
                return 0
            else:
                # STEP 2.2 VLOOKUP(B36-D14*X33,B36:W185,COLUMN(Q36)-COLUMN(B36)+6)
                _val_idx = (year - payment_term * run_times)
                g_acc = g_accs[_val_idx]
                # STEP 3 g_acc*(1+H6)^X33
                return g_acc * (1 + repeat_saving_growth_rate) ** run_times

    # **
    # 计算 nG acc
    # Params:
    # 翌年歲 C36 : ANB
    # 年期 B35 : Year
    # EGI index D17 : 169
    # 实际比率 D23: Actual ratio
    # 显示汇率 D27: Display rate
    def get_nG_acc(self, anb, year, actual_ratio, display_rate):
        # 参考计算公式
        # =IF(C36<=151,VLOOKUP(B35,data4!A2:HI153,D17+5,FALSE)*D23*D27,0)
        res = dict_db.vlookup_EGI_by_anb_and_col(args=[year, anb], col=EgiObj.EGI['nG_acc'])
        if not res['code'] == 200:
            return 0
        actual_ratio = float(round(actual_ratio, 6))
        cal_nG_acc = res['data']
        if not anb <= 151:
            return 0
        else:
            # STEP 2 cal_G_m_income *D23*D27
            return cal_nG_acc * actual_ratio * display_rate

    # **
    # 计算 nG acc
    # Params:
    # 重複儲蓄計劃 H5 : Repeat Saving Plan
    # 重複儲蓄增長率 H6: Repeat Saving Growth Rate
    # X次数 X33 : run_times
    # 年期 B36 : Year
    # 供款期 D14: Payment Term
    # 显示汇率 Q36: Display rate
    def get_nG_acc_x(self, year, repeat_saving_plan, run_times, repeat_saving_growth_rate, payment_term, nG_accs, ):
        # =IF(H5>=X33,IF(B36>D14*X33,VLOOKUP(B36-D14*X33,B36:W185,COLUMN(Q36)-COLUMN(B36)+7)*(1+H6)^X33,0),0)
        # STEP 1 VLOOKUP(B36-D14*X33,B36:W185,COLUMN(Q36)-COLUMN(B36)+7) ==> nG_acc
        if not repeat_saving_plan >= run_times:
            return 0
        else:
            # STEP 2.1 IF(B36>D14*X33,VLOOKUP(B36-D14*X33,B36:W185,COLUMN(Q36)-COLUMN(B36)+7)*(1+H6)^X33,0)
            if not year > payment_term * run_times:
                return 0
            else:
                # STEP 2.2 VLOOKUP(B36-D14*X33,B36:W185,COLUMN(Q36)-COLUMN(B36)+7)
                _val_idx = (year - payment_term * run_times)
                nG_acc = nG_accs[_val_idx]
                # STEP 3 nG_acc*(1+H6)^X33
                return nG_acc * (1 + repeat_saving_growth_rate) ** run_times

    # **
    # 说明:
    @staticmethod
    def egi_master(str_json_key, args: list[str], years):
        import numpy as np
        from datetime import datetime
        TEST_SELECT_PAYMENT = int(args[0])  # Payment Term 供款期
        TEST_SELECT_ANB = int(args[1])  # ANB 翌年歲
        TEST_CURRENCY = args[2]  # Currency 貨幣
        TEST_CURRENCY = TEST_CURRENCY.replace("_", "-")
        TEST_IRR_ANB = int(args[3])  # IRR 翌年歲
        TEST_CASH_PAYMENT_IRR = float(args[4])  # 現金提取 IRR
        TEST_ACCUMULATION_IRR = float(args[5])  # 積存 IRR
        TEST_SAVING = float(args[6])  # 儲蓄金額
        TEST_REPEAT_SAVING_PLAN = float(args[7])  # 重複儲蓄計劃
        TEST_REPEAT_SAVING_GROWTH_RATE = float(args[8])  # 重複儲蓄增長率
        TEST_PROMOTION = args[9]
        calStd = EgiBaseTabCalStd()
        try:
            # 获取外部参数
            cal_display_rate = base_table_top.get_Display_rate(currency=TEST_CURRENCY)
            cal_usd_target_premium = base_table_top.get_USD_target_premium(saving=TEST_SAVING,
                                                                           display_rate=cal_display_rate)
            cal_actual_annual_contribution = base_table_top.get_Actual_annual_contribution(
                usd_target_premium=int(cal_usd_target_premium))
            cal_actual_ratio = base_table_top.get_Actual_ratio(
                actual_annual_contribution=cal_actual_annual_contribution)
            # ----------------------------------------------------------------------------------------------------------
            rows = []
            tot_array_g_m_income = {}
            tot_array_ng_m_income = {}
            tot_array_g_cv_income = {}
            tot_array_terminal_income = {}
            tot_array_g_acc_income = {}
            tot_array_ng_acc_income = {}
            # ----------------------------------------------------------------------------------------------------------
            last_Remain = 1  # 这里是循环数据处理的前置数据
            now = datetime.now()
            current_time = now.strftime("%H:%M:%S")
            print("当前时间 =", current_time)
            save_db_flg = True
            # ----------------------------------------------------------------------------------------------------------
            for test_year in years:  # TEST_YEAR:
                if test_year == 0:
                    continue
                single_row_data = {}  # 页面数据行
                print("run test Year with : %d" % test_year)
                cal_anb = TEST_SELECT_ANB + test_year
                # ------------------------------------------------------------------------------------------------------
                # Col 1 年期
                one_Year = test_year
                single_row_data['Year_res'] = one_Year
                # Col 2 翌年歲
                one_ANB = cal_anb
                single_row_data['ANB_res'] = one_ANB
                # Col 3 每年供款
                one_Annual_Contribution = float(0)
                # Col 4 現金提取 入息
                one_Cash_Payment_Surrender_Value = float(0)
                # Col 5 現金提取 累積入息
                one_Cash_Payment_Income = float(0)
                # Col 6 現金提取 退保價值
                one_Cash_Payment_Cumulative_Income = float(0)
                # Col 7 積存 入息
                one_Accumulation_Income = float(0)
                # Col 8 積存 積存結餘
                one_Accumulation_Balance = float(0)
                # Col 9 積存 退保價值
                one_Accumulation_Surrender_Value = float(0)
                # Col 12 Withdraw cashflow
                one_Withdraw_cashflow = 0
                # Col 13 Accu cashflow
                one_Accu_cashflow = 0
                one_Rebate = 0
                if test_year == 1:
                    one_Rebate = calStd.get_Rebate(promotion=TEST_PROMOTION,
                                                   actual_annual_contribution=cal_actual_annual_contribution,
                                                   display_rate=cal_display_rate)
                # Col 5
                one_Contribution = calStd.get_Contribution(year=test_year,
                                                           payment_term=TEST_SELECT_PAYMENT,
                                                           actual_annual_contribution=cal_actual_annual_contribution,
                                                           display_rate=cal_display_rate)
                single_row_data['one_Contribution_res'] = round(one_Contribution, 0)
                # Col 6
                # 首列数据在数据表中确认
                one_G_m_income = calStd.get_G_m_income(anb=cal_anb,
                                                       year=test_year,
                                                       actual_ratio=cal_actual_ratio,
                                                       display_rate=cal_display_rate)
                single_row_data['one_G_m_income_res'] = round(one_G_m_income, 4)
                tot_array_g_m_income[test_year] = one_G_m_income
                # Col 7
                one_nG_m_income = calStd.get_nG_m_income(anb=cal_anb,
                                                         year=test_year,
                                                         actual_ratio=cal_actual_ratio,
                                                         display_rate=cal_display_rate)
                single_row_data['one_nG_m_income_res'] = round(one_nG_m_income, 4)
                tot_array_ng_m_income[test_year] = one_nG_m_income
                # Col 8
                one_G_CV = calStd.get_G_CV(anb=cal_anb,
                                           year=test_year,
                                           actual_ratio=cal_actual_ratio,
                                           display_rate=cal_display_rate)
                single_row_data['one_G_CV_res'] = round(one_G_CV, 0)
                tot_array_g_cv_income[test_year] = one_G_CV
                # Col 9
                one_Terminal = calStd.get_Terminal(anb=cal_anb,
                                                   year=test_year,
                                                   actual_ratio=cal_actual_ratio,
                                                   display_rate=cal_display_rate)
                single_row_data['one_Terminal_res'] = round(one_Terminal, 0)
                tot_array_terminal_income[test_year] = one_Terminal
                # Col 10
                one_G_acc = calStd.get_G_acc(anb=cal_anb,
                                             year=test_year,
                                             actual_ratio=cal_actual_ratio,
                                             display_rate=cal_display_rate)
                single_row_data['one_G_acc_res'] = round(one_G_acc, 0)
                tot_array_g_acc_income[test_year] = one_G_acc
                # Col 11
                one_nG_acc = calStd.get_nG_acc(anb=cal_anb,
                                               year=test_year,
                                               actual_ratio=cal_actual_ratio,
                                               display_rate=cal_display_rate)
                single_row_data['one_nG_acc_res'] = round(one_nG_acc, 0)
                tot_array_ng_acc_income[test_year] = one_nG_acc
                # 这里进行次幂运算
                for run_times in range(5):
                    run_times = run_times + 1
                    if len(rows) > 0:
                        Contribution_x = [item["one_Contribution_res"] for item in rows]
                    else:
                        Contribution_x = [one_Contribution]
                    # Col 12,19,26,33,40
                    one_Contribution_x = calStd.get_Contribution_x(year=test_year,
                                                                   repeat_saving_plan=TEST_REPEAT_SAVING_PLAN,
                                                                   run_times=run_times,
                                                                   repeat_saving_growth_rate=TEST_REPEAT_SAVING_GROWTH_RATE,
                                                                   payment_term=TEST_SELECT_PAYMENT,
                                                                   contributions=Contribution_x)
                    str_attr_name = "one_Contribution_%d_res" % run_times
                    single_row_data[str_attr_name] = one_Contribution_x
                    # Col 13,20,27,34,41
                    one_G_m_income_x = calStd.get_G_m_income_x(year=test_year,
                                                               repeat_saving_plan=TEST_REPEAT_SAVING_PLAN,
                                                               run_times=run_times,
                                                               repeat_saving_growth_rate=TEST_REPEAT_SAVING_GROWTH_RATE,
                                                               payment_term=TEST_SELECT_PAYMENT,
                                                               g_m_incomes=tot_array_g_m_income)
                    str_attr_name = "one_G_m_income_%d_res" % run_times
                    single_row_data[str_attr_name] = round(one_G_m_income_x, 4)
                    # Col 14,21,28,35,42
                    one_nG_m_income_x = calStd.get_nG_m_income_x(year=test_year,
                                                                 repeat_saving_plan=TEST_REPEAT_SAVING_PLAN,
                                                                 run_times=run_times,
                                                                 repeat_saving_growth_rate=TEST_REPEAT_SAVING_GROWTH_RATE,
                                                                 payment_term=TEST_SELECT_PAYMENT,
                                                                 nG_m_incomes=tot_array_ng_m_income)
                    str_attr_name = "one_nG_m_income_%d_res" % run_times
                    single_row_data[str_attr_name] = round(one_nG_m_income_x, 4)
                    # Col 15,22,29,36,43
                    one_G_CV_x = calStd.get_G_CV_x(year=test_year, repeat_saving_plan=TEST_REPEAT_SAVING_PLAN,
                                                   run_times=run_times,
                                                   repeat_saving_growth_rate=TEST_REPEAT_SAVING_GROWTH_RATE,
                                                   payment_term=TEST_SELECT_PAYMENT, g_cvs=tot_array_g_cv_income)
                    str_attr_name = "one_G_CV_%d_res" % run_times
                    single_row_data[str_attr_name] = round(one_G_CV_x, 0)
                    # Col 16,23,30,37,44
                    one_Terminal_x = calStd.get_Terminal_x(year=test_year, repeat_saving_plan=TEST_REPEAT_SAVING_PLAN,
                                                           run_times=run_times,
                                                           repeat_saving_growth_rate=TEST_REPEAT_SAVING_GROWTH_RATE,
                                                           payment_term=TEST_SELECT_PAYMENT,
                                                           terminals=tot_array_terminal_income)
                    str_attr_name = "one_Terminal_%d_res" % run_times
                    single_row_data[str_attr_name] = round(one_Terminal_x, 4)
                    # Col 17,24,31,38,45
                    one_G_acc_x = calStd.get_G_acc_x(year=test_year, repeat_saving_plan=TEST_REPEAT_SAVING_PLAN,
                                                     run_times=run_times,
                                                     repeat_saving_growth_rate=TEST_REPEAT_SAVING_GROWTH_RATE,
                                                     payment_term=TEST_SELECT_PAYMENT, g_accs=tot_array_g_acc_income)
                    str_attr_name = "one_G_acc_%d_res" % run_times
                    single_row_data[str_attr_name] = round(one_G_acc_x, 4)
                    # Col 18,25,32,39,46
                    one_nG_acc_x = calStd.get_nG_acc_x(year=test_year, repeat_saving_plan=TEST_REPEAT_SAVING_PLAN,
                                                       run_times=run_times,
                                                       repeat_saving_growth_rate=TEST_REPEAT_SAVING_GROWTH_RATE,
                                                       payment_term=TEST_SELECT_PAYMENT,
                                                       nG_accs=tot_array_ng_acc_income)
                    str_attr_name = "one_nG_acc_%d_res" % run_times
                    single_row_data[str_attr_name] = round(one_nG_acc_x, 4)

                # Col 3 每年供款
                arry_contribution = [single_row_data[f"one_Contribution_%d_res" % (_idx + 1)] for _idx in range(5)]
                arry_contribution.append(single_row_data['one_Contribution_res'])
                total_contribution = np.sum(arry_contribution)
                one_Annual_Contribution = calStd.get_Annual_Contribution(anb=cal_anb,
                                                                         total_contribution=total_contribution,
                                                                         rebate=one_Rebate)
                single_row_data["one_Annual_Contribution_res"] = round(one_Annual_Contribution, 2)
                # Col 4 現金提取 入息
                arry_g_m_income = []
                arry_ng_m_income = []
                arry_g_m_income.append(single_row_data['one_G_m_income_res'])
                arry_ng_m_income.append(single_row_data['one_nG_m_income_res'])
                for _idx in range(5):
                    arry_g_m_income.append(single_row_data[f"one_G_m_income_%d_res" % (_idx + 1)])
                    arry_ng_m_income.append(single_row_data[f"one_nG_m_income_%d_res" % (_idx + 1)])
                total_g_m_income = np.sum(arry_g_m_income)
                total_ng_m_income = np.sum(arry_ng_m_income)
                one_Cash_Payment_Income = calStd.get_Cash_Payment_Income(anb=cal_anb,
                                                                         total_g_m_income=total_g_m_income,
                                                                         total_ng_m_income=total_ng_m_income)
                single_row_data["one_Cash_Payment_Income_res"] = round(one_Cash_Payment_Income, 0)
                # Col 5 現金提取 累積入息
                last_Cash_Payment_Cumulative_Income = float(0)
                if len(rows) > 0:
                    last_row = rows[len(rows) - 1]
                    last_Cash_Payment_Cumulative_Income = last_row['one_Cash_Payment_Cumulative_Income_res']
                one_Cash_Payment_Cumulative_Income = calStd.get_Cash_Payment_Cumulative_Income(anb=cal_anb,
                                                                                               cumulative_income=last_Cash_Payment_Cumulative_Income,
                                                                                               income=one_Cash_Payment_Income)
                single_row_data["one_Cash_Payment_Cumulative_Income_res"] = round(one_Cash_Payment_Cumulative_Income, 0)
                # Col 6 現金提取 退保價值
                arry_g_cv = [single_row_data[f"one_G_CV_%d_res" % (_idx + 1)] for _idx in range(5)]
                arry_g_cv.append(single_row_data['one_G_CV_res'])
                total_g_cv = np.sum(arry_g_cv)
                arry_terminal = [single_row_data[f"one_Terminal_%d_res" % (_idx + 1)] for _idx in range(5)]
                arry_terminal.append(single_row_data['one_Terminal_res'])
                total_terminal = np.sum(arry_terminal)
                one_Cash_Payment_Surrender_Value = calStd.get_Cash_Payment_Surrender_Value(anb=cal_anb,
                                                                                           total_g_cv=total_g_cv,
                                                                                           total_terminal=total_terminal)
                single_row_data["one_Cash_Payment_Surrender_Value_res"] = round(one_Cash_Payment_Surrender_Value, 0)
                # Col 7 積存 入息
                one_Accumulation_Income = calStd.get_Accumulation_Income(year=test_year, income=one_Cash_Payment_Income)
                single_row_data["one_Accumulation_Income_res"] = round(one_Accumulation_Income, 0)
                # Col 8 積存 積存結餘
                arry_g_acc = [single_row_data[f"one_G_acc_%d_res" % (_idx + 1)] for _idx in range(5)]
                arry_g_acc.append(single_row_data['one_G_acc_res'])
                total_g_acc = np.sum(arry_g_acc)
                arry_ng_acc = [single_row_data[f"one_nG_acc_%d_res" % (_idx + 1)] for _idx in range(5)]
                arry_ng_acc.append(single_row_data['one_nG_acc_res'])
                total_ng_acc = np.sum(arry_ng_acc)
                one_Accumulation_Balance = calStd.get_Accumulation_Cumulative_Income(anb=cal_anb,
                                                                                     total_g_acc=total_g_acc,
                                                                                     total_ng_acc=total_ng_acc)
                single_row_data["one_Accumulation_Balance_res"] = round(one_Accumulation_Balance, 0)
                # Col 9 積存 退保價值
                one_Accumulation_Surrender_Value = calStd.get_Accumulation_Surrender_Value(anb=cal_anb,
                                                                                           surrender_value=one_Cash_Payment_Surrender_Value,
                                                                                           accumulation_balance=one_Accumulation_Balance)
                single_row_data["one_Accumulation_Surrender_Value_res"] = round(one_Accumulation_Surrender_Value, 0)
                # Col 12 Withdraw cashflow
                last_Cash_Payment_Income = 0
                last_Cash_Payment_Surrender_Value = 0
                if len(rows) > 0:
                    last_row = rows[len(rows) - 1]
                    last_Cash_Payment_Income = last_row['one_Cash_Payment_Income_res']
                    last_Cash_Payment_Surrender_Value = last_row['one_Cash_Payment_Surrender_Value_res']
                one_Withdraw_cashflow = calStd.get_Withdraw_cashflow(anb=cal_anb,
                                                                     irr_anb=TEST_IRR_ANB,
                                                                     last_income=last_Cash_Payment_Income,
                                                                     income=one_Cash_Payment_Income,
                                                                     annual_contribution=one_Annual_Contribution,
                                                                     surrender_value=last_Cash_Payment_Surrender_Value)
                single_row_data["one_Withdraw_cashflow_res"] = round(one_Withdraw_cashflow, 2)
                # Col 13 Accu cashflow
                last_Accumulation_Income = 0
                last_Accumulation_Surrender_Value = 0
                if len(rows) > 0:
                    last_row = rows[len(rows) - 1]
                    last_Accumulation_Income = last_row['one_Accumulation_Income_res']
                    last_Accumulation_Surrender_Value = last_row['one_Accumulation_Surrender_Value_res']
                one_Accu_cashflow = calStd.get_Accu_cashflow(anb=cal_anb,
                                                             irr_anb=TEST_IRR_ANB,
                                                             last_income=last_Accumulation_Income,
                                                             income=one_Accumulation_Income,
                                                             annual_contribution=one_Annual_Contribution,
                                                             surrender_value=last_Accumulation_Surrender_Value)
                single_row_data["one_Accu_cashflow_res"] = round(one_Accu_cashflow, 2)
                rows.append(single_row_data)
            # ----------------------------------------------------------------------------------------------------------
            now_2 = datetime.now()
            current_time_2 = now_2.strftime("%H:%M:%S")
            print("当前时间 =", current_time_2)
            str_json_data = json.dumps(rows, ensure_ascii=False)
            if save_db_flg:
                dict_db.insert_cal_json_data([str_json_key, str_json_data, str(datetime.now().strftime("%f"))])
            # 保持一致同步，写入到RDS中
            RedisConfig.push_val_intimes(rds_cfg, key=f'freeL_hash_key:{str_json_key}', val=str_json_data,
                                         json_flg=True, inTimes=60 * 60)
            return {"code": 200, "data": str_json_data}
        except Exception as e:
            print(e)
